package com.smarttest.portal.refresh.onlinequery.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.g7s.test.utils.common.CommonUtils;
import com.g7s.test.utils.common.JdbcUtils;
import com.smarttest.common.util.DateTimeUtils;
import com.smarttest.portal.beans.InterfaceDetail;
import com.smarttest.portal.mapper.ZentaobugcountMapper;
import com.smarttest.portal.pojo.Zentaobugcount;
import com.smarttest.portal.pojo.ZentaobugcountExample;
import com.smarttest.portal.pojo.ZentaobugcountExample.Criteria;
import com.smarttest.portal.pojo.ZentaobugcountExample.Criterion;
import com.smarttest.portal.refresh.onlinequery.dao.ZentaoDao;
@Service
public class ZentaoDaoImpl implements ZentaoDao {

	private DataSource zentaoDataSource;
	@Autowired
	ZentaobugcountMapper zentaobugcountMapper;
	/**
	 * set注入
	 * @param dataSource
	 */
	@Resource(name = "dataSourceZentao")
	protected void setDataSource(DataSource dataSource) {
		this.zentaoDataSource = dataSource;
	}

	@Override
	public int updateBugTolocalNow() {
		Connection conn = null;
		int temp = 0;
		try {
			conn = zentaoDataSource.getConnection();
			JdbcUtils jb = new JdbcUtils(conn);
			String sql_getproduct = "select id,name from `zt_product` where deleted = '0'";
			String sql,sqlurl1,sqlurl2,sqlurl3;
			List<Map<String, Object>> l = jb.findModeResult(sql_getproduct);		
			String todaystart = DateTimeUtils.getDayStart(DateTimeUtils.GetDateTimeStamp(),0).toString();
			String todayend = DateTimeUtils.getDayEnd(DateTimeUtils.GetDateTimeStamp(),0).toString();		
			for (Map<String, Object> m : l) {
				sqlurl1="";
				sqlurl2="";
				sqlurl3 = "";
				sql = "select count(*) as count from (SELECT t1.*,t2.name FROM  zt_bug t1 JOIN `zt_product` t2 on t1.product = t2.id WHERE t1.deleted = '0'";
				int proid = (int)m.get("id");
				sql += " and t2.id = " + (int)m.get("id"); 
				sqlurl1 = sql + " and t1.status != 'closed' ) t";
				sqlurl2 = sql + " and t1.openedDate between '" + todaystart + "' and '" + todayend +"') t";
				sqlurl3 = sql + " and t1.resolvedDate between '" + todaystart + "' and '" + todayend+"') t";
				int opened = Integer.parseInt(jb.findSimpleResult(sqlurl1).get("count").toString());
				int newd = Integer.parseInt(jb.findSimpleResult(sqlurl2).get("count").toString());
				int resolved = Integer.parseInt(jb.findSimpleResult(sqlurl3).get("count").toString());
				Zentaobugcount zentaocount = new Zentaobugcount();
				zentaocount.setProjectid(proid);
				zentaocount.setProjectname(m.get("name").toString());
				zentaocount.setOpencount(opened);
				zentaocount.setNewcount(newd);
				zentaocount.setResolvecount(resolved);
				zentaocount.setStatistictime(DateTimeUtils.daysStart(0));
				ZentaobugcountExample example = new ZentaobugcountExample();
				Criteria criteria = example.createCriteria();
				criteria.andProjectidEqualTo(proid);
				criteria.andStatistictimeEqualTo(DateTimeUtils.daysStart(0));
				if (zentaobugcountMapper.selectByExample(example).size() > 0) {
					temp = zentaobugcountMapper.updateByExampleSelective(zentaocount, example);
				}
				else {
					temp = zentaobugcountMapper.insert(zentaocount);
				}
			}
		}
		catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			temp = -1;
		}
		return temp;
	}
	
	@Override
	public void getYesterdayBugTolocal() {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
			conn = zentaoDataSource.getConnection();
			JdbcUtils jb = new JdbcUtils(conn);
			String sql_getproduct = "select id,name from `zt_product` where deleted = '0'";
			String sql,sqlurl1,sqlurl2,sqlurl3;
			List<Map<String, Object>> l = jb.findModeResult(sql_getproduct);		
			String yesterdaystart = DateTimeUtils.getDayStart(DateTimeUtils.GetDateTimeStamp(),-1).toString();
			String yesterdayend = DateTimeUtils.getDayEnd(DateTimeUtils.GetDateTimeStamp(),-1).toString();
			for (Map<String, Object> m : l) {
				sqlurl1="";
				sqlurl2="";
				sqlurl3 = "";
				sql = "select count(*) as count from (SELECT t1.*,t2.name FROM  zt_bug t1 JOIN `zt_product` t2 on t1.product = t2.id WHERE t1.deleted = '0'";
				int proid = (int)m.get("id");
				sql += " and t2.id = " + (int)m.get("id"); 
				sqlurl1 = sql + " and t1.status != 'closed' ) t";
				sqlurl2 = sql + " and t1.openedDate between '" + yesterdaystart + "' and '" + yesterdayend +"') t";
				sqlurl3 = sql + " and t1.resolvedDate between '" + yesterdaystart + "' and '" + yesterdayend+"') t";
				int opened = Integer.parseInt(jb.findSimpleResult(sqlurl1).get("count").toString());
				int newd = Integer.parseInt(jb.findSimpleResult(sqlurl2).get("count").toString());
				int resolved = Integer.parseInt(jb.findSimpleResult(sqlurl3).get("count").toString());
				Zentaobugcount zentaocount = new Zentaobugcount();
				zentaocount.setProjectid(proid);
				zentaocount.setProjectname(m.get("name").toString());
				zentaocount.setOpencount(opened);
				zentaocount.setNewcount(newd);
				zentaocount.setResolvecount(resolved);
				zentaocount.setStatistictime(DateTimeUtils.daysStart(-1));
				ZentaobugcountExample example = new ZentaobugcountExample();
				Criteria criteria = example.createCriteria();
				criteria.andProjectidEqualTo(proid);
				criteria.andStatistictimeEqualTo(DateTimeUtils.daysStart(-1));
				if (zentaobugcountMapper.selectByExample(example).size() > 0) {
					zentaobugcountMapper.updateByExampleSelective(zentaocount, example);
				}
				else {
					zentaobugcountMapper.insert(zentaocount);
				}
			}
		}
		catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	@Override
	public String getBugToString(Integer projecid) {
		// TODO Auto-generated method stub
		String bugsStr = "时间,未关闭BUG数,当天新增,当天解决";
		SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd");		
		String sql = "SELECT statistictime,sum(opencount) as allopencount,SUM(newcount) as allnewcount,SUM(resolvecount) as allresolvecount FROM `zentaobugcount` group by statistictime;";
		if (projecid >= 0) {
			ZentaobugcountExample example = new ZentaobugcountExample();
			example.setOrderByClause("statistictime asc");
			Criteria criteria = example.createCriteria();
			criteria.andStatistictimeGreaterThanOrEqualTo(DateTimeUtils.daysStart(-61));
			criteria.andStatistictimeLessThanOrEqualTo(DateTimeUtils.daysEnd(0));
			criteria.andProjectidEqualTo(projecid);
			List<Zentaobugcount> list = zentaobugcountMapper.selectByExample(example);
			for (Zentaobugcount zc : list) {
				bugsStr += "\n" + sdf.format(zc.getStatistictime()) +"," +zc.getOpencount() +","+ zc.getNewcount() +","+ zc.getResolvecount();
			}
		}		
		else {
			List<LinkedHashMap<String, Object>> list = zentaobugcountMapper.superManagerSelect(sql);
			for (LinkedHashMap<String, Object> map : list) {
				bugsStr += "\n" + sdf.format((Date)map.get("statistictime")) +"," +map.get("allopencount") +","+ map.get("allnewcount") +","+ map.get("allresolvecount");
			}
		}	

		return bugsStr;
	}
	@Override
	public String getProducts() {
		// TODO Auto-generated method stub
		ZentaobugcountExample example = new ZentaobugcountExample();
		example.setOrderByClause("projectid asc");
		example.setDistinct(true);
		List<Zentaobugcount> list = zentaobugcountMapper.selectProductByExample(example);
		String bugsStr = "<option value=\"-1\">-ALL-</option>";
		for (Zentaobugcount zc : list) {
			if (zc.getProjectid() == 4) {
				bugsStr += "<option value=\""+zc.getProjectid()+"\" selected=\"selected\">"+zc.getProjectname()+"</option>";
			}
			else {
				bugsStr += "<option value=\""+zc.getProjectid()+"\">"+zc.getProjectname()+"</option>";
			}
		}
		return bugsStr;
	}
}
